/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Account;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Cham to tet
 */
public class AccountDAO {

    public int addAccount(Account acc) {
        int result = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("Insert into Account values(?,?,?,?,?)");
            if (acc.getCompanyID() == 0) {
                stm.setNull(1, java.sql.Types.INTEGER);
            } else {
                stm.setInt(1, acc.getCompanyID());
            }
            if (acc.getDriversID() == 0) {
                stm.setNull(2, java.sql.Types.INTEGER);
            } else {
                stm.setInt(2, acc.getDriversID());
            }
            stm.setString(3, acc.getEmail());
            stm.setString(4, acc.getPassword());
            stm.setBoolean(5, false);
            result = stm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    public Account getUser(Account account) {
        Account a = new Account();
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stmt = con.prepareStatement("select * from Account where Email= '" + account.getEmail() + "'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                a = new Account();
                a.setID(rs.getInt(1));
                a.setCompanyID(rs.getInt(2));
                a.setDriversID(rs.getInt(3));
                a.setEmail(rs.getString(4));
                a.setPassword(rs.getString(5));
                a.setStatus(rs.getBoolean(6));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return a;
    }
    
    public static List<Account> displayAccount() throws Exception {
        ResultSet rs = null;
        List listing = new ArrayList();
        Connection con = (Connection) DBConnection.myConnection();
        PreparedStatement stm = con.prepareStatement("Select * from Account");
        rs = stm.executeQuery();
        Account a = null;
        while (rs.next()) {
            a = new Account();
            a.setID(rs.getInt(1));
            a.setCompanyID(rs.getInt(2));
            a.setDriversID(rs.getInt(3));
            a.setEmail(rs.getString(4));
            a.setPassword(rs.getString(5));
            a.setStatus(rs.getBoolean(6));
            listing.add(a);
        }
        con.close();
        return listing;
        
    }
    
    public void deleteAccount(int id){
        try{
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("Delete from Account Where ID=" +id);
            st.executeQuery();
            con.close();
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }
}
